using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;
using MySqlConnector;
using RFS.BizLogic.Sys.impl;
using RFS.BizLogic.utils;
using RFS.Domain.PaiShi;
using RFS.Domain.PaiShi.Accounting;
using RFS.Domain.PaiShi.Zuhuo;
using RFS.Domain.Sys;
using ycx;

namespace RFS.BizLogic.PaiShi.Ports.Adapters
{
    public class ZahuaRepoMySqlAdapter : IRepoZahua
    {
        private string _srcConnectionString = "";
        private string _targetConnectionString = "";

        private const string TargetZaTableName = "acc_tbl_选包扎花表";
        private const string SrcZaHuaTable = "选包扎花表_有效";


        private ILog _log;
        private IConfigT _config;

        public ZahuaRepoMySqlAdapter(ILog log, IConfigT config)
        {
            CreategpDic();
            _log = log;
            _config = config;

            _srcConnectionString = ConfigurationManager.ConnectionStrings["CnnStrDama"].ConnectionString;
            _targetConnectionString = ConfigurationManager.ConnectionStrings["CnnStrAccDb"].ConnectionString;

            tryCreateTable();
        }


        public void ReCreateTables(string pwd)
        {
            if (pwd == "a")
            {
                ExecuteSqlQueryInTargetDb($"DROP TABLE {TargetZaTableName};");
            }


            tryCreateTable();
        }

        private void tryCreateTable()
        {
            //           var sql =
            // $@"select    序号, 组号, 扎花打码, 打码时间, 选包是否扫码, 扫码时间, 版本号 FROM {TargetZaTableName} z where date(z.扫码时间)='{date:yyyy-MM-dd}' ORDER BY  序号 desc  ";
            var sql1 = $@"
   CREATE TABLE IF NOT EXISTS `{TargetZaTableName}` (
    `Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `序号` BIGINT UNSIGNED NOT NULL UNIQUE ,
        `EntityJson` LONGTEXT   NULL,
         `BizDate` DATETIME NOT NULL,
    `ZhiLingItemId` BIGINT UNSIGNED   ,

     
    PRIMARY KEY (`Id`)
)   DEFAULT CHARSET=utf8mb4;
";


            ExecuteSqlQueryInTargetDb(sql1);
        }


        public List<dynamic> ExecuteSqlQueryInTargetDb(string query)
        {
            List<dynamic> results = new List<dynamic>();

            using (MySqlConnection connection = new MySqlConnection(_targetConnectionString))
            {
                connection.Open();

                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dynamic obj = new ExpandoObject();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                var fieldName = reader.GetName(i);
                                var fieldValue = reader[i];
                                ((IDictionary<string, object>)obj).Add(fieldName, fieldValue);
                            }

                            results.Add(obj);
                        }
                    }
                }
            }

            return results;
        }


        List<dynamic> ExecuteSqlQueryInSrcDb(string query)
        {
            List<dynamic> results = new List<dynamic>();

            using (MySqlConnection connection = new MySqlConnection(_srcConnectionString))
            {
                connection.Open();

                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dynamic obj = new ExpandoObject();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                var fieldName = reader.GetName(i);
                                var fieldValue = reader[i];
                                ((IDictionary<string, object>)obj).Add(fieldName, fieldValue);
                            }

                            results.Add(obj);
                        }
                    }
                }
            }

            return results;
        }


        //
        // public void SaveEntity(ZaHuaItem o)
        // {
        // }
        //
        //
        // public void DeleteEntity(AVSEntity o)
        // {
        //     var dic = o.ToAccItemAndOrderBasedDicItem();
        //
        //     using (var connection = new MySqlConnection(_connectionString))
        //     {
        //         connection.Open();
        //
        //         using (var tran = connection.BeginTransaction())
        //         {
        //             #region de entity
        //
        //             var sql1 = $"DELETE FROM {EntityTableName} WHERE ID = {o.Id}";
        //             using (var command = new MySqlCommand(sql1, connection, tran))
        //             {
        //                 command.ExecuteNonQuery();
        //             }
        //
        //             #endregion
        //
        //             #region de dic for search
        //
        //             if (dic.Count() > 0)
        //             {
        //                 var sql4 =
        //                     $"delete from  {DicTableName} where AccItemId in ({dic.Select(x => x.AccItemId).JoinToString()})";
        //                 using (var command = new MySqlCommand(sql4, connection, tran))
        //                 {
        //                     command.ExecuteNonQuery();
        //                 }
        //             }
        //
        //             #endregion
        //
        //             tran.Commit();
        //         }
        //
        //         connection.Close();
        //     }
        // }
        //
        // public AVSEntity GetById(ulong id)
        // {
        //     var sql = $"select id,entityJson FROM {EntityTableName} WHERE ID = {id}";
        //     var obj = ExecuteSqlQuery(sql);
        //     if (obj.Count == 0) throw new Exception($"找不到记录【{id}】。");
        //
        //     var json = $"{obj[0].entityJson}";
        //     var en = json.ToObject<AVSEntity>();
        //     return en;
        // }


        /// <summary>
        /// 移除空字节
        /// </summary>
        /// <param name="bytes"></param>
        /// <returns></returns>
        public string ByteTostring(byte[] bytes)
        {
            List<byte> dataList = bytes.ToList();
            dataList.RemoveAll(b => b == 0);
            byte[] cleanedData = dataList.ToArray();
            string result = Encoding.UTF8.GetString(cleanedData);
            return result;
        }


        void SetBizType(ZaHuaItem za, DateTime wd)
        {
            if (za.供货日期.Date == wd.Date)
            {
                za.业务类型 = "当期";
            }
            else
            {
                za.业务类型 = "历史";
            }
        }

        public IEnumerable<ZaHuaItem> GetRecordsByDay(DateTime day, bool includeHistLeftOver)
        {
            var date = day;
//date(z.扫码时间)='2025-1-14',  also inlude the history left over record
            var sql =
                $@"SELECT z.*, zm.id ZhiLingItemId2,zu.ZHTime ZHTime   FROM {TargetZaTableName} z 
    left join  acc_tbl_组花明细表 zm on zm.ZaHuaItemId=z.Id 
   left join acc_tbl_组花主表 zu on zm.ParentId=zu.Id
                                   
                                   
          WHERE 
                                                   date(z.BizDate)='{date:yyyy-MM-dd}' 
                                               OR ( {includeHistLeftOver} AND (
                                                    date(z.BizDate)<'{date:yyyy-MM-dd}'  AND zm.Id is null ) 
                                                    )
                                               ORDER BY  序号 desc  ";
            var dobj = ExecuteSqlQueryInTargetDb(sql);
            var res = dobj.Select(x =>
            {
                var re = $"{x.EntityJson}".ToObject<ZaHuaItem>();
                re.IsEmtpy = false;
                SetBizType(re, day);

                if (!Convert.IsDBNull(x.ZHTime))
                    1.TryRun(() => re.指令时间 = x.ZHTime);

                if (!Convert.IsDBNull(x.ZhiLingItemId2))
                    1.TryRun(() => re.ZhiLingItemId = x.ZhiLingItemId2);

                re.IsSavedInDb = true;
                return re;
            }).ToList();

            return res;
        }


        public IEnumerable<ZaHuaItem> TransferRestRecordsForToAccRepoByDay(DateTime day)
        {
            var date = day;


            //1 get all ids from target,
            //2 get data from src db accept the ids,
            //3 write into the target db.

            var idsExisted =
                ExecuteSqlQueryInTargetDb(
                        $@"select    序号  FROM {TargetZaTableName} z where date(z.BizDate)='{date:yyyy-MM-dd}'   ")
                    .Select(x => (ulong)x.序号)
                    .ToList();
            idsExisted.Add(9); // in case of empty


            //date(z.扫码时间)=  fro src db
            var sql =
                $@"select    序号, 组号, 扎花打码, 打码时间, 选包是否扫码, 扫码时间, 版本号 FROM {SrcZaHuaTable} z where date(z.扫码时间)='{date:yyyy-MM-dd}' and (序号 not in ({idsExisted.JoinToString()})) ORDER BY  序号 desc  ";
            var dobj = ExecuteSqlQueryInSrcDb(sql);
            var res = dobj.Select(x =>
            {
                var re = new ZaHuaItem()
                {
                    IdBackPointer = (ulong)x.序号,
                    扎花打码 = ByteTostring(x.扎花打码),
                };

                var p = ParseCode(re.扎花打码);


                re.地块 = p.dk;
                re.货品编码 = $"HP{re.地块.ToUpper().Replace("DK", "")}";
                re.品种 = p.pzCode;
                re.长度 = $"{p.len}";
                re.规格 = p.gg;
                re.供货商码 = DKToGpCodeTry(p.dk);
                re.供货日期 = x.打码时间;
                re.IsEmtpy = false;

                SetBizType(re, day);

                return re;
            }).ToList();

            _log.Info($"已同步扎花数据【{res.Count()}】条");
            SaveEntitysAsync(res, false);

            return res;
        }


        Dictionary<string, string> _gpcodeDic = new Dictionary<string, string>();


        public class Plot
        {
            public string 地块名称 { get; set; }
            public string 拍供编码 { get; set; }
        }


        string DKToGpCodeTry(string dk)
        {
            if (_gpcodeDic.ContainsKey(dk)) return _gpcodeDic[dk];
            else
            {
                return "";
            }
        }

        private void CreategpDic()
        {
            // JSON data as a string (this would be your JSON input)
            string jsonData = json; // 省略了中间的部分以保持简洁

            // Deserialize the JSON string into a list of Plot objects
            List<Plot> plots = jsonData.ToObject<List<Plot>>();

            // Create a dictionary with 地块名称 as the key and Plot object as the value
            Dictionary<string, string> plotDictionary = new Dictionary<string, string>();
            foreach (var plot in plots)
            {
                plotDictionary[plot.地块名称] = plot.拍供编码;
            }

            _gpcodeDic = plotDictionary;
        }


        private string json = $@"
[
    {{""地块名称"": ""DK01"", ""拍供编码"": ""30301""}},
    {{""地块名称"": ""DK02"", ""拍供编码"": ""30302""}},
    {{""地块名称"": ""DK03"", ""拍供编码"": ""30303""}},
    {{""地块名称"": ""DK04"", ""拍供编码"": ""30304""}},
    {{""地块名称"": ""DK05"", ""拍供编码"": ""30305""}},
    {{""地块名称"": ""DK06"", ""拍供编码"": ""30306""}},
    {{""地块名称"": ""DK07"", ""拍供编码"": ""30307""}},
    {{""地块名称"": ""DK08"", ""拍供编码"": ""30308""}},
    {{""地块名称"": ""DK09"", ""拍供编码"": ""30309""}},
    {{""地块名称"": ""DK10"", ""拍供编码"": ""30310""}},
    {{""地块名称"": ""DK11"", ""拍供编码"": ""30311""}},
    {{""地块名称"": ""DK12"", ""拍供编码"": ""30312""}},
    {{""地块名称"": ""DK13"", ""拍供编码"": ""30313""}},
    {{""地块名称"": ""DK14"", ""拍供编码"": ""30314""}},
    {{""地块名称"": ""DK15"", ""拍供编码"": ""30315""}},
    {{""地块名称"": ""DK16"", ""拍供编码"": ""30316""}},
    {{""地块名称"": ""DK17"", ""拍供编码"": ""30317""}},
    {{""地块名称"": ""DK18"", ""拍供编码"": ""30318""}},
    {{""地块名称"": ""DK19"", ""拍供编码"": ""30319""}},
    {{""地块名称"": ""DK20"", ""拍供编码"": ""30320""}},
    {{""地块名称"": ""DK21"", ""拍供编码"": ""30321""}},
    {{""地块名称"": ""DK22"", ""拍供编码"": ""30322""}},
    {{""地块名称"": ""DK23"", ""拍供编码"": ""30323""}},
    {{""地块名称"": ""DK24"", ""拍供编码"": ""30324""}},
    {{""地块名称"": ""DK25"", ""拍供编码"": ""30325""}},
    {{""地块名称"": ""DK26"", ""拍供编码"": ""30326""}},
    {{""地块名称"": ""DK27"", ""拍供编码"": ""30327""}},
    {{""地块名称"": ""DK28"", ""拍供编码"": ""30328""}},
    {{""地块名称"": ""DK29"", ""拍供编码"": ""30329""}},
    {{""地块名称"": ""DK30"", ""拍供编码"": ""30330""}},
    {{""地块名称"": ""DK31"", ""拍供编码"": ""30331""}},
    {{""地块名称"": ""DK32"", ""拍供编码"": ""30332""}},
    {{""地块名称"": ""DK33"", ""拍供编码"": ""30333""}},
    {{""地块名称"": ""DK34"", ""拍供编码"": ""30334""}},
    {{""地块名称"": ""DK35"", ""拍供编码"": ""30335""}},
    {{""地块名称"": ""DK36"", ""拍供编码"": ""30336""}},
    {{""地块名称"": ""DK37"", ""拍供编码"": ""30337""}},
    {{""地块名称"": ""DK38"", ""拍供编码"": ""30347""}},
    {{""地块名称"": ""DK39"", ""拍供编码"": ""30339""}},
    {{""地块名称"": ""DK40"", ""拍供编码"": ""30340""}},
    {{""地块名称"": ""DK41"", ""拍供编码"": ""30341""}},
    {{""地块名称"": ""DK42"", ""拍供编码"": ""30342""}},
    {{""地块名称"": ""DK43"", ""拍供编码"": ""30343""}},
    {{""地块名称"": ""DK44"", ""拍供编码"": ""30344""}},
    {{""地块名称"": ""DK45"", ""拍供编码"": ""30345""}},
    {{""地块名称"": ""DK46"", ""拍供编码"": ""30346""}},
    {{""地块名称"": ""DK47"", ""拍供编码"": ""30348""}},
    {{""地块名称"": ""DK48"", ""拍供编码"": ""30349""}},
    {{""地块名称"": ""DK49"", ""拍供编码"": ""30350""}}
]
";

        // zahua code to infos
        //007.04.55.10-T25-C01-DK22_02:P1F_0018
        // PZcode.DENGJI.LEN.GG-TIANGANG-DK-
        public (string dk, string pzCode, string len, string gg) ParseCode(string sn)
        {
            // 输入字符串
            string inputString = sn;
            // 定义正则表达式模式
            string pattern =
                @"^(?<part1>\d+)\.(?<part2>\d+)\.(?<part3>\d+)\.(?<part4>\d+)-(?<part5>[A-Za-z]\d+)-(?<part6>[A-Z\d]+)-(?<part7_prefix>[A-Za-z\d]+)_(?<part7_suffix>\d+):(?<part8>[A-Za-z\d]+)_(?<part9>\d+)$";

            // 创建 Regex 对象
            Regex regex = new Regex(pattern);

            // 执行匹配
            Match match = regex.Match(inputString);

            if (match.Success)
            {
                #region comment

                // 提取捕获组中的值
                // Console.WriteLine("Part 1: " + match.Groups["part1"].Value);
                // Console.WriteLine("Part 2: " + match.Groups["part2"].Value);
                // Console.WriteLine("Part 3: " + match.Groups["part3"].Value);
                // Console.WriteLine("Part 4: " + match.Groups["part4"].Value);
                // Console.WriteLine("Part 5: " + match.Groups["part5"].Value);
                // Console.WriteLine("Part 6: " + match.Groups["part6"].Value);
                // Console.WriteLine("Part 7 Prefix: " + match.Groups["part7_prefix"].Value); // DK22
                // Console.WriteLine("Part 7 Suffix: " + match.Groups["part7_suffix"].Value); // 02
                // Console.WriteLine("Part 8: " + match.Groups["part8"].Value);
                // Console.WriteLine("Part 9: " + match.Groups["part9"].Value);
                /*
Part 1: 007 pzCode
Part 2: 04 dengji
Part 3: 55  len
Part 4: 10  guige
Part 5: T25  taingang
Part 6: C01
Part 7 Prefix: DK22 DK
Part 7 Suffix: 02
Part 8: P1F
Part 9: 0018
                 */

                #endregion

                // (string dk, string pz, string len, string pzCode)
                return (
                    match.Groups["part7_prefix"].Value,
                    match.Groups["part1"].Value,
                    match.Groups["part3"].Value,
                    match.Groups["part4"].Value);
            }
            else
            {
                throw new Exception($"[{inputString}] 不能解析");
            }


            return default;
        }


        public async Task SaveEntityAsync(ZaHuaItem o, bool check4Update = true)
        {
            await SaveEntitysAsync(new List<ZaHuaItem>() { o }, check4Update);

            // await Task.Run(() =>
            // {
            //     using (var connection = new MySqlConnection(_targetConnectionString))
            //     {
            //         connection.Open();
            //
            //         connection.EnlistTransaction(System.Transactions.Transaction.Current);
            //         using (var tran = connection.BeginTransaction())
            //         {
            //             try
            //             {
            //                 SaveDb(o, check4Update, connection, tran);
            //                 tran.Commit();
            //                 connection.Close();
            //                 _log.Info($"【1】扎已保存.");
            //             }
            //             catch (Exception e)
            //             {
            //                 _log.Err(e);
            //             }
            //
            //         }
            //     }
            //
            // });
        }

        public IEnumerable<ZaHuaItem> SyncZahuaDataFromProductionLine(DateTime day)
        {
            return TransferRestRecordsForToAccRepoByDay(day);
        }

        public ZaHuaItem CreateNewZahua()
        {
            var z = new ZaHuaItem()
            {
                地块 = _config.ConfigSys.FuzuDiKuai,
                供货商码 = _config.ConfigSys.FuzuGongyingShangMa,
                Id = 1.NewSnowflakeId(),
                品种 = "003",
                货品编码 = "HP01",
                长度 = "45",
                规格 = "10",
                供货日期 = DateTime.Now,
                业务类型 = "当期",
            };

            z.IdBackPointer = z.Id;
            return z;
        }

        public void Del(IEnumerable<ZaHuaItem> zaHuaItems)
        {
            if (zaHuaItems.Count() < 1) return;

            var sql1 = $"DELETE FROM {TargetZaTableName} WHERE ID in ({zaHuaItems.Select(x => x.Id).JoinToString()})";
            ExecuteSqlQueryInTargetDb(sql1);

            _log.Info($"成功删除{zaHuaItems.Count()}扎");
        }

        public async Task SaveEntitysAsync(IEnumerable<ZaHuaItem> os, bool check4Update = true)
        {
            // Replace with your actual connection string and object list
            var connectionString = _targetConnectionString;
            var objects = os.ToList();
            var objectSaver = new ObjectSaver<ZaHuaItem>(connectionString, objects, 80, _log);
            await objectSaver.SaveObjectsAsync((c, t, o) => { SaveDb(o, true, c, t); });
        }


        // public async Task SaveEntitysAsync(IEnumerable<ZaHuaItem> os, bool check4Update = true)
        // {
        //     
        //     if (os==null || os.Count()<1 ) return;
        //     await Task.Run(() =>
        //     {
        //         using (var connection = new MySqlConnection(_targetConnectionString))
        //         {
        //             connection.Open();
        //
        //             using (var tran = connection.BeginTransaction())
        //             {
        //                 foreach (var o in os)
        //                 {
        //                     SaveDb(o, check4Update, connection, tran);
        //                 }
        //                 _log.Info($"【{os.Count()}】扎已保存.");
        //
        //                 tran.Commit();
        //                 connection.Close();
        //             }
        //         }
        //         
        //     });
        // }

        object _locksave = new object();

        private void SaveDb(ZaHuaItem o, bool check4Update, MySqlConnection connection, MySqlTransaction tran)
        {
           
           
             if (o.IsSavedInDb) 
                 SaveUpdate2Db( o,connection,tran);
             else 
                 SaveInsert2Db(o,connection,tran);
            
            
            
        }

        private void SaveInsert2Db(ZaHuaItem o,   MySqlConnection connection, MySqlTransaction tran)
        {


            // o.Id = 1.NewSnowflakeId();
            o.IsSavedInDb = true;
            string sql = $@"
            INSERT INTO {TargetZaTableName} (id, 序号, EntityJson, BizDate, ZhiLingItemId)
                                    VALUES (@id, @序号, @EntityJson, @BizDate, @ZhiLingItemId);
        ";

            using (var command = new MySqlCommand(sql, connection, tran))
            {
                command.Parameters.AddWithValue("@id", o.Id);
                command.Parameters.AddWithValue("@序号", o.IdBackPointer);
                command.Parameters.AddWithValue("@entityJson", o.ToJson());
                command.Parameters.AddWithValue("@BizDate", o.供货日期);
                command.Parameters.AddWithValue("@ZhiLingItemId", o.ZhiLingItemId);
                command.ExecuteNonQuery();
            }
 
        }
        
        
        private void SaveUpdate2Db(ZaHuaItem o,   MySqlConnection connection, MySqlTransaction tran)
        {
            string sql = $@"
            UPDATE   {TargetZaTableName}  SET   序号=@序号 , EntityJson=@EntityJson, BizDate=@BizDate, ZhiLingItemId=@ZhiLingItemId 
            where Id=@id
        ";

            using (var command = new MySqlCommand(sql, connection, tran))
            {
                command.Parameters.AddWithValue("@id", o.Id);
                command.Parameters.AddWithValue("@序号", o.IdBackPointer);
                command.Parameters.AddWithValue("@entityJson", o.ToJson());
                command.Parameters.AddWithValue("@BizDate", o.供货日期);
                command.Parameters.AddWithValue("@ZhiLingItemId", o.ZhiLingItemId);
                command.ExecuteNonQuery();
            }
 
        }
        
        
        
    }
}